SQL Server Performance Tuning

TLDR

• Use SSMS "Include Actual Execution Plan" and "Display Estimated Execution Plan" to identify performance bottlenecks and missing indexes.
• Consolidate similar index requirements when creating indexes to avoid excessive indexing, which can degrade write performance.
• Clustered indexes determine physical ordering, while non-clustered indexes serve as secondary indexes.
• Use INCLUDE to add non-key columns to indexes, reducing data page reads.
• Composite indexes must follow the "Left-Prefix Rule" to be utilized effectively.
• To handle locking conflicts, choose between NOLOCK, NOWAIT, or READPAST based on business requirements.
• Parameter Sniffing issues can be resolved via Query Store plan forcing, OPTIMIZE FOR, RECOMPILE, or clearing the plan cache.

Analyzing Queries with SSMS Execution Plan Tools

When tuning query performance, you can use the execution plan tools in SSMS for diagnostics.

Execution Plan Mode Differences

• Include Actual Execution Plan: Displays actual statistics (e.g., elapsed time, read volume) after the query executes; suitable for optimizing queries that have already been run.
• Display Estimated Execution Plan: Estimates the execution path based on statistics without actually running the query; suitable for analyzing queries that have not been run or take too long to execute.

Identifying and Adding Missing Indexes

When query performance is poor, you can right-click the execution plan and select "Missing Index Details." The system will automatically generate the suggested CREATE INDEX syntax.

WARNING

Do not blindly create all suggested indexes. Many index requirements can be consolidated, and excessive indexes will significantly reduce data write performance.

To identify missing indexes from system views, you can use the following query:

SELECT TOP 20
    CONVERT (varchar(30), getdate(), 126) AS runtime,
    CONVERT (decimal (28, 1),
        migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)
        ) AS estimated_improvement,
    'CREATE INDEX missing_index_' +
        CONVERT (varchar, mig.index_group_handle) + '_' +
        CONVERT (varchar, mid.index_handle) + ' ON ' +
        mid.statement + ' (' + ISNULL (mid.equality_columns, '') +
        CASE
            WHEN mid.equality_columns IS NOT NULL
            AND mid.inequality_columns IS NOT NULL THEN ','
            ELSE ''
        END + ISNULL (mid.inequality_columns, '') + ')' +
        ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement
FROM sys.dm_db_missing_index_groups mig
JOIN sys.dm_db_missing_index_group_stats migs ON
    migs.group_handle = mig.index_group_handle
JOIN sys.dm_db_missing_index_details mid ON
    mig.index_handle = mid.index_handle
ORDER BY estimated_improvement DESC;

SQL Server Index Fundamentals

Indexes are based on a B-Tree structure and are divided into Clustered Indexes and Non-Clustered Indexes.

• Clustered Index: Determines the physical ordering of data; each table can have only one. Using a GUID as a key is not recommended; a sequential number is more appropriate.
• Non-Clustered Index: Acts as a secondary index, containing key values and pointers to the clustered index.
• Purpose of INCLUDE: Including non-key columns in a non-clustered index can reduce the number of reads from data pages, improving query efficiency.
• Left-Prefix Rule: Composite indexes are only effective when the query condition includes the leftmost contiguous columns of the index. If the query condition does not meet this rule, the SQL Server optimizer typically will not use the index.

Handling Query Locking

When a query is blocked due to locking, you can choose a handling strategy based on business requirements:

• NoLock: Reads data while ignoring locks; may read uncommitted data (dirty reads).
• NoWait: Returns an error immediately without waiting for the lock to be released.
• ReadPast: Skips locked rows and only reads available data.

Parameter Sniffing Issues and Solutions

Parameter Sniffing occurs when the database generates an execution plan based on the parameter value used during the first execution. If that parameter value has extreme characteristics, performance may drop significantly when subsequent different parameters are used.

Identifying Performance Issues

If a parameterized query runs slowly, but performance returns to normal when changed to a non-parameterized query (with values hardcoded), it is highly likely to be affected by this.

Solutions

• Query Store: The recommended solution for SQL Server 2016+, which allows fixing a stable execution plan via "Force Plan."
• OPTION (OPTIMIZE FOR): Forces the optimizer to generate a plan for a specific value or average distribution (UNKNOWN).
• OPTION (RECOMPILE): Recompiles the query every time it executes; suitable for complex queries that run infrequently but have high variance.
• Clear Plan Cache: Use DBCC FREEPROCCACHE to remove old plans and force regeneration.

To identify potential Parameter Sniffing queries, you can analyze execution time variance:

SELECT TOP 20
    t.text AS [SQL Text],
    st.execution_count,
    [Max Elapsed (ms)] = st.max_elapsed_time / 1000,
    [Avg Elapsed (ms)] = (st.total_elapsed_time / st.execution_count) / 1000,
    [Max/Avg Ratio] = CAST(st.max_elapsed_time * 1.0 / NULLIF(st.total_elapsed_time / st.execution_count, 0) AS DECIMAL(10,2)),
    st.plan_handle,
    st.last_execution_time
FROM sys.dm_exec_query_stats AS st
CROSS APPLY sys.dm_exec_sql_text(st.sql_handle) AS t
WHERE t.dbid = DB_ID('{YourDatabaseName}')
  AND st.execution_count > 50
ORDER BY [Max/Avg Ratio] DESC;

Change Log

• 2023-03-15 Initial document creation.
• 2026-01-01
  • Fixed an error in the Parameter Sniffing detection script where the logic was the opposite of the expected result.
  • Added additional solutions for Parameter Sniffing.